# -*- coding: utf-8 -*-
# @Time: 2024/12/4 5:40
# @Author: foxhuty
# @File: new_exams_arrangement.py

import pandas as pd
import os
import re


def load_data(file_path):
    try:
        data = pd.read_excel(file_path, dtype={'考号': str, '考生号': str})
        clean_data = clean_spaces(data)
        return clean_data
    except FileNotFoundError:
        print(f"Error: The file at {file_path} was not found.")
        raise
    except Exception as e:
        print(f"An error occurred while reading the file: {e}")
        raise


def exam_rooms_arrangement(data, num_in_room):
    """
    用于教室安排，生成考生所在考室号和座位号，同时生成考生座签
    :param num_in_room: 考室人数
    :param data: 用于生成考室安排的原始成绩表return: 考生所在考室号和座位号df和考生座签df
    """
    # 获取名为考号或考生号的列
    candidate_num_col = [col for col in data.columns if col in ['考号', '考生号']][0]
    # 把考号列或考生号列转换为字符串类型，以便考号或考生号能完全显示出来
    data[candidate_num_col] = data[candidate_num_col].astype(str)
    # 按总分排序分配考室号，增加次级排序条件
    data.sort_values(by=['总分', candidate_num_col], ascending=[False, True], inplace=True)
    # 生成考室号和座位号
    data['考室号'] = [f'第{(i // num_in_room) + 1}考室' for i in range(data.shape[0])]
    data['座位号'] = [f'{(i % num_in_room) + 1}' for i in range(data.shape[0])]

    # 获取考室df
    exam_rooms_df = data.loc[:, ['班级', '姓名', candidate_num_col, '考室号', '座位号']]
    exam_rooms_df.reset_index(drop=True, inplace=True)

    # 生成考生座签
    exam_seat = exam_rooms_df.copy()
    for i in range(exam_seat.shape[0]):
        exam_seat.loc[i + 0.5] = ['班级', '姓名', candidate_num_col, '考室号', '座位号']
    exam_seat.sort_index(inplace=True, ignore_index=True)

    output_dir = 'D:\\成绩统计结果'
    if not os.path.exists(output_dir):
        os.makedirs(output_dir)

    output_path = os.path.join(output_dir, 'exam_info.xlsx')
    try:
        with pd.ExcelWriter(output_path) as writer:
            exam_seat.to_excel(writer, index=False, sheet_name='座签号')
            exam_rooms_df.to_excel(writer, index=False, sheet_name='考室安排表')
    except Exception as e:
        print(f"An error occurred while writing to the file: {e}")
        raise

    return exam_rooms_df, exam_seat


def clean_spaces(data):
    # 清洗列名中的空格
    data.columns = [col.strip() for col in data.columns]
    data.columns = [col.replace(' ', '') for col in data.columns]
    # 清洗数据中的空格
    for col in data.columns:
        if data[col].dtype == 'object':  # 只处理字符串类型的列
            data[col] = data[col].str.strip()
    return data


def remove_spaces_between_chars(text):
    return re.sub(r'\s+', '', text)


if __name__ == '__main__':
    file = r'D:\data_test_files\高2023级上期末考试成绩数据.xlsx'
    exam_df = load_data(file)
    exam_rooms_arrangement(exam_df, 35)
